/*
	DECLARE @STATUS VARCHAR(800) 
	EXEC SP_SAVE_FORECAST_SHEET_DATA 'Marlin', 'Marlin', 'Marlin-Marlin-iDTV', '8G13-B1', 2007, 9, 200, NULL, 'admin', @STATUS OUTPUT
	PRINT @STATUS

	THE OUT PARAMETER @STATUS_CODE VALUE SHOULD BE POWERS OF 2 i.e 1,2,4,8,16... SINCE THESE ARE HANDLING BITWISE
*/
ALTER PROC SP_SAVE_FORECAST_SHEET_DATA ( @SALES_PERSON VARCHAR(50), --NOT USED
					@CUSTOMER_NAME VARCHAR(50), -- NOT USED
					@PROJ_NAME VARCHAR(50),
					@PART_NUMBER VARCHAR(50),
					@YEAR INT,
					@MONTH INT,
					@QTY INT,
					@PRICE FLOAT,
					@COMMENTS VARCHAR(800),
					@USER_ID VARCHAR(50),
					@STATUS_CODE INT OUT )
AS
BEGIN
	DECLARE @SALESPERSON_ID INT
	DECLARE @PROJ_SPERSON_ID INT
	DECLARE @PRODUCT_ID INT
	DECLARE @CUSTOMER_ID INT
	DECLARE @ODMID INT
	DECLARE @PROJ_ID INT
	DECLARE @DUMMY_ADDRESS_ID INT
	SET @STATUS_CODE = 0

	
	SELECT @PRODUCT_ID = P.PRODUCT_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PART_NUMBER = @PART_NUMBER AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND PS.PS_TYPE_NAME = 'Finished Goods'
	IF ( @PRODUCT_ID IS NULL ) --CHECKING IN EQUIVALENT PART NUMBER
	 BEGIN
		SELECT @PRODUCT_ID = P.PRODUCT_ID FROM PRODUCT P, PROCESS_STEP PS, PRODUCT_MAP_TABLE PMT WHERE PMT.PART_NUMBER = @PART_NUMBER AND PS.PROCESSSTEP_ID = PMT.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
	 END
	IF( @PRODUCT_ID IS NULL )
	 BEGIN
		--SET @STATUS_CODE = 'Part Number ''' + @PART_NUMBER + ''' does not exist' --PRODUCT DOES NOT EXISTS
		SET @STATUS_CODE = 1
		RETURN
	 END
/*
	SELECT @SALESPERSON_ID = SALESPERSON_ID FROM SALES_PERSONS WHERE SALESPERSON_NAME = @SALES_PERSON
	IF ( @SALESPERSON_ID IS NULL )
	 BEGIN
		INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TAG_NAME, TIMESTAMP )
				VALUES ( '', '', '', '', '', '', '', '', '', '', '', '','', CURRENT_TIMESTAMP )
		SELECT @DUMMY_ADDRESS_ID = @@IDENTITY

		INSERT INTO SALES_PERSONS( SalesPerson_Name, Deactivated, Address_ID, Sales_Percent, Region_ID ) VALUES ( @SALES_PERSON, 0, @DUMMY_ADDRESS_ID, 0.0, 0 )
		SELECT @SALESPERSON_ID = @@IDENTITY		
		SET @STATUS_CODE = @STATUS_CODE | 2
	 END
*/
/*
	SELECT @CUSTOMER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME = @CUSTOMER_NAME
	IF( @CUSTOMER_ID IS NULL )
	 BEGIN
		EXECUTE SP_SAVE_CUSTOMER @CUSTOMER_NAME, @CUSTOMER_NAME, NULL, 'Customer', '', 0, @CUSTOMER_ID OUTPUT, '', '', ''
		IF ( @CUSTOMER_ID IS NULL )
		 BEGIN
			--SET @STATUS_CODE = 'Could not create customer ''' + @CUSTOMER_NAME + ''' '--CUSTOMER DOES NOT EXISTS
			SET @STATUS_CODE = @STATUS_CODE | 4
			RETURN
		 END
		ELSE
		 BEGIN
			SET @STATUS_CODE = @STATUS_CODE | 8
		 END

	 END
*/
	SELECT @PROJ_ID = PROJID, @SALESPERSON_ID = SPERSONID, @CUSTOMER_ID = CUSTID, @ODMID = ODMID, @PROJ_SPERSON_ID = SPERSONID FROM PROJSETUP WHERE PROJNAME = @PROJ_NAME

/*	IF ( @PROJ_ID IS NULL ) --NEED TO CREATE PROJECT
	 BEGIN
		INSERT INTO PROJSETUP ( PROJNAME, CUSTID, PROJDISABLED, SPERSONID ) VALUES ( @PROJ_NAME, @CUSTOMER_ID, 1, @SALESPERSON_ID ) 
		SET @STATUS_CODE = @STATUS_CODE | 16
		SELECT @PROJ_ID = @@IDENTITY
	 END
	ELSE IF ( @SALESPERSON_ID != @PROJ_SPERSON_ID ) --PROJECT EXISTS WITH DIFFERENT SALES PERSON
	 BEGIN
		--SET @STATUS_CODE = 'Project ''' + @PROJ_NAME + ''' Exists with other sales person' --PROJECT EXISTS WITH DIFFERENT SALES PERSON
		SET @STATUS_CODE = @STATUS_CODE | 32
		RETURN
	 END
*/	
	IF ( @PROJ_ID IS NULL )
	 BEGIN
		SET @STATUS_CODE = 2
		RETURN
	 END

	IF ( (SELECT COUNT(*) FROM PROJPRODREL WHERE PROJID = @PROJ_ID AND PRODID = @PRODUCT_ID) = 0 ) --PRODUCT NOT MAPPED WITH THIS PROJ
	 BEGIN
		SET @STATUS_CODE = 3
		RETURN  
	 END

	DECLARE @PULL_TYPE INT
	DECLARE @ROW_ID INT
	DECLARE @PULLED_PRICE FLOAT
	DECLARE @ROW_PRICE FLOAT

	IF(@PRICE = 0 )
	 BEGIN
		SET @PULLED_PRICE = 0.0
		SELECT @PULL_TYPE = ISNULL(PRICE_PULL,1) FROM SYSTEMPRFS		

		IF (@PULL_TYPE = 1)
		BEGIN
			SELECT @PULLED_PRICE = PPT_UNIT_PRICE FROM PRODUCT_PRICE_TABLE WHERE PRODUCT_ID = @PRODUCT_ID AND (@QTY BETWEEN PPT_QTY_FROM AND PPT_QTY_TO)
		END
		ELSE IF (@PULL_TYPE = 2)
		BEGIN
			SELECT @PULLED_PRICE = CPT_UNIT_PRICE FROM CUSTOMER_PRICING_TABLE WHERE PRODUCT_ID = @PRODUCT_ID AND (@QTY BETWEEN CPT_QTY_FROM AND CPT_QTY_TO)
		END
		SET @PRICE = @PULLED_PRICE
	 END
	
	SET @ROW_ID = NULL
	SELECT @ROW_ID = ROWID, @ROW_PRICE = PRICE FROM FCENTRYMASTER WHERE PROJID = @PROJ_ID AND PRODID = @PRODUCT_ID AND [MONTH] = @MONTH AND [YEAR] = @YEAR

	IF( @ROW_ID IS NULL ) --FORECAST NOT EXIST FOR THIS PROJ, PROD, MONTH, YEAR
	 BEGIN
		INSERT INTO FCENTRYMASTER ( SPERSONID, PRODID, CUSTID, ODMID, PROJID, MONTH, YEAR, QTY, PRICE,CONFIDENCE_LEVEL, COMMENTS, USERID, ENTRYDATE )
			VALUES ( @SALESPERSON_ID, @PRODUCT_ID, @CUSTOMER_ID, ISNULL(@ODMID,@CUSTOMER_ID), @PROJ_ID, @MONTH, @YEAR ,@QTY ,@PRICE, 0.0, @COMMENTS, @USER_ID, CURRENT_TIMESTAMP )

		INSERT INTO FCENTRYHISTORY ( ROWID, SPERSONID, PRODID, CUSTID, ODMID,PROJID, 
						MONTH, QUARTER, YEAR, QTY, PRICE,CONFIDENCE_LEVEL, USERLIST1, 
						USERDATE1, USERTXT1,USERTXT2, USERID, ENTRYDATE, COMMENTS )
				( SELECT ROWID, SPERSONID, PRODID, CUSTID, ODMID,PROJID,MONTH, QUARTER, YEAR, QTY, 
					PRICE,CONFIDENCE_LEVEL, USERLIST1, USERDATE1,USERTXT1,USERTXT2, USERID, 
					ENTRYDATE, COMMENTS FROM FCENTRYMASTER WHERE ROWID = @@IDENTITY )
	 END
	ELSE
	 BEGIN
		--copy to history table
		INSERT INTO FCENTRYHISTORY ( ROWID, SPERSONID, PRODID, CUSTID, ODMID,PROJID, 
					MONTH, QUARTER, YEAR, QTY, PRICE,CONFIDENCE_LEVEL, USERLIST1, 
					USERDATE1, USERTXT1,USERTXT2, USERID, ENTRYDATE, COMMENTS )
			( SELECT ROWID, SPERSONID, PRODID, CUSTID, ODMID,PROJID,MONTH, QUARTER, YEAR, 
				QTY, PRICE,CONFIDENCE_LEVEL, USERLIST1, USERDATE1, USERTXT1,USERTXT2, 
				USERID, ENTRYDATE, COMMENTS FROM FCENTRYMASTER WHERE ROWID = @ROW_ID )
		
		--now update
		UPDATE FCENTRYMASTER SET QTY = @QTY, PRICE = @PRICE, CONFIDENCE_LEVEL = 0.0, COMMENTS = @COMMENTS, USERID = @USER_ID, ENTRYDATE = CURRENT_TIMESTAMP WHERE ROWID = @ROW_ID
	 END	
END